package com.g2room.myuall.tools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.g2room.myuall.grap.ky.model.po.PeeUnivInfo;
import com.g2room.myuall.util.DbUtil;

public class DownloadKyImgs {
	public static void main(String[] args) {
		List<PeeUnivInfo> univs = getImages();
		String logoPath = "";
		String bannerPath = "";
		for(PeeUnivInfo ui : univs) {
			logoPath = null;
			bannerPath=null;
			try {
				logoPath = ImageUtil.downloadImage2Ky(ui.getLogo(), "logos");
			} catch (Exception e) {
				if(!e.toString().contains("FileNotFoundException")) {
					e.printStackTrace();
				}
			} finally {
				insertImgs(ui.getId(), ui.getLogo(), logoPath, "logo");
			}
			try {
				bannerPath = ImageUtil.downloadImage2Ky(ui.getBanner(), "banners");
			} catch (Exception e) {
				if(!e.toString().contains("FileNotFoundException")) {
					e.printStackTrace();
				}
			} finally {
				insertImgs(ui.getId(), ui.getBanner(), bannerPath, "banner");
			}
		}
		
	}

	public static List<PeeUnivInfo> getImages() {
		List<PeeUnivInfo> univs = new ArrayList<PeeUnivInfo>();
		List<Integer> uids = getImageIds();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select id,code from pee_univ_info";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				int uid = rs.getInt("id");
				if(uids.contains(uid)) continue;
				String code = rs.getString("code");
				PeeUnivInfo univ = new PeeUnivInfo();
				univ.setId(uid);
				univ.setLogo("http://img.kaoyan.com/school/logo/"+code+"_logo.png");
				univ.setBanner("http://img.kaoyan.com/school/banner/timg_"+code+".jpg");
				univs.add(univ);
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return univs;
	}
	


	public static List<Integer> getImageIds() {
		List<Integer> univs = new ArrayList<Integer>();
		try {
			Connection connection = DbUtil.getConn();
			Statement statement = connection.createStatement();
			String sql = "select school_id from grap_imgs";
			ResultSet rs = statement.executeQuery(sql);
			while(rs.next()) {
				univs.add(rs.getInt("school_id"));
			}
			rs.close();
			statement.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		return univs;
	}

	public static void insertImgs(int uid, String oldUrl, String url, String type) {
		PreparedStatement grapPs = null;
		try {
			grapPs = DbUtil.getConn().prepareStatement("insert into grap_imgs(school_id, old_url, url, type) value(?, ?, ?, ?)");
			// id, old_url, url, type
			grapPs.setInt(1, uid);
			grapPs.setString(2, oldUrl);
			grapPs.setString(3, url);
			grapPs.setString(4, type);
			grapPs.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if(null != grapPs) {
				try {
					grapPs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}
	
}
